Slack Workflow Builder & Spreadsheet: How to change the timestamp to a different time zone

sanni

2023.08.03

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Moi! This is Sanni from Classmethod Group's contract support team.

I usually write blogs in Japanese but I made a discovery with Slack's Workflow Builder timestamp time zone and I'd like to share it with a wider audience. This is meant for those who are already familiar with Workflow Builder as I'm not going write how to make a workflow from scratch.

Hope you find this helpful!

Addressing the problem

First, these are my workflow steps. Very simple - open a form, add a spreadsheet row, send a message, update a spreadsheet row. I use this workflow model for task inquiries from the sales team, such as mailing documents and preparing contracts.

What I want to do is to have a timestamp when the inquiry was made, as well as when it was completed. This is to keep track of monthly inquiries as well as how fast tasks were completed.

Here is the simple way to do this; for Timestamp column I inserted a value of "Time when step was completed" from the "Send a form" step.

Here I sent the form, as you can see the time is 11:43.

However when I look at the spreadsheet, the time is 02:43:01. That's a 9 hour difference, which means the spreadsheet timestamp is in Greenwich Mean Time, as I am based in Japan. (GMT+9)

Unfortunately whatever your Slack settings are, Workflow Builder will only use GMT. On top of that, the timestamp can't be changed to Date time format.

But with few settings and functions we can make this work!!

Changing spreadsheet settings

On your spreadsheet go to File>Settings, and change your Locale to United Kingdom, and Time zone to(GMT+00:00)London.

Now you can change the format to Date time (dd/mm/yyyy hh:mm:ss), which is more universal.

Adding a function

Next step is to add a function on Workflow Builder, so when the transcription to a spreadsheet will automatically do the function.

=("[Variable]"+"9:00")

For [Variable], Insert a variable of "Time when step was completed" from the "Send a form" step.

As you can see I sent the form at 12:50.

And looking at the spreadsheet the timestamp is 12:50:27. Perfect!

I also tried this with different time zone, GMT-8:00 which is Pacific Time Zone, just to make sure it will recognize change of date.

=("[Variable]"-"8:00")

Here I sent the form on August 3rd at 12:59, GMT+9:00.

And here is the timestamp, correctly 17 hours behind on August 2nd at 19:59, GMT-8:00.

Updating a spreadsheet

When you update a spreadsheet row, for "Find a row" choose the Timestamp column and for "Define a cell value to find" use the same function as you did before. This way it will find the exact row.

For updating columns, I used the same function for Completion time column, except the variable is "Time when step was completed" for "Send a message" step. Be careful not to mix them up!